<?php
/***************************************************************
*  Copyright notice
*  (c) 2009, jianyuzhu@gmail.com
*  All rights reserved
*  This script is part of the PPEMI project.
***************************************************************/

class ADODBDatabase {
	var $database = '';
	var $dbType = 'mysql';
	var $dbHostName = '';
	var $dbUserName = '';
	var $dbPassword = '';
	var $dbName = '';
	var $lastmysqlrow = -1;
	
	//
	var $enableSQLlog = false;
	var $dieOnError = false;
	
	//query time
	var $queryTime = 0;
	
	//query count from the page start to the end
	//internal counter
	var $queryCount = 0;
	
	//
	var $_link = '_db_link';
	
	//constructor
	function &getInstance() {
		static $instance;
		if( !is_object($instance) ) {
			$instance = & new ADODBDatabase();
		}
		return $instance;
	}
	
	function isMySQL() {
		return ($this->dbType == 'mysql');
	}
	
	function setDbType($dbType) {
		$this->dbType = $dbType;
	}
	
	function getDbType() {
		return $this->dbType;
	}
	
	function setDbHostName($dbHostName) {
		$this->dbHostName = $dbHostName;
	}
	
	function getDbHostName() {
		return $this->dbHostName;
	}
	
	function setDbUserName($dbUserName) {
		$this->dbUserName = $dbUserName;
	}
	
	function getDbUserName() {
		return $this->dbUserName;
	}
	
	function setDbPassword($dbPassword) {
		$this->dbPassword = $dbPassword;
	}
	
	function getDbPassword() {
		return $this->dbPassword;
	}
	
	function setDbName($dbName) {
		$this->dbName = $dbName;
	}
	
	function getDbName() {
		return $this->dbName;
	}
	
	function change_key_case($arr) {
		return is_array($arr)?array_change_key_case($arr):$arr;
	}
	
	//construction
	function ADODBDatabase($dbtype = '', $hostname = '', $dbname = '', $username = '', $password = '') {
		$this->initSettings();
		$this->resetSettings($dbtype, $hostname, $dbname, $username, $password);
	}
	
	function initSettings() {
		$this->db_type = CONFIG_DB_TYPE;
		$this->db_host = CONFIG_DB_HOST;
		$this->db_user = CONFIG_DB_USER;
		$this->db_password = CONFIG_DB_PASSWORD;
		$this->db_name = CONFIG_DB_NAME;
		$this->db_prefix = CONFIG_DB_PREFIX;
		$this->db_pconnect = CONFIG_DB_PCONNECT;
		$this->db_logsql = CONFIG_DB_LOGSQL;
		$this->db_logfile = CONFIG_DB_LOGFILE;
	}
	
	function resetSettings($dbtype = '', $hostname = '', $dbname = '', $username = '', $password = '') {
		if( $hostname == '' ) {
			$this->disconnect();
			$this->setDbType($this->db_type);
			$this->setDbHostName($this->db_host);
			$this->setDbUserName($this->db_user);
			$this->setDbPassword($this->db_password);
			$this->setDbName($this->db_name);
			if( $this->db_logsql ) {
				$this->enableSQLlog = ($this->db_logsql == true);
			}
		} else {
			$this->disconnect();
			$this->setDbType($dbtype);
			$this->setDbHostName($hostname);
			$this->setDbUserName($username);
			$this->setDbPassword($password);
			$this->setDbName($dbname);
		}
	}
	
	function connect($link = '_db_link') {
		if( !isset($this->dbType) ) {
			return false;
		}
		global $$link;
		
		if( $this->db_pconnect == 'true' ) {
			$$link = mysql_pconnect($this->dbHostName, $this->dbUserName, $this->dbPassword) or die('DBECP');
		} else {
			$$link = mysql_connect($this->dbHostName, $this->dbUserName, $this->dbPassword) or die('DBEC');
		}
		
		if( $$link ) {
			mysql_select_db($this->dbName) or die('DBEDB');
			$this->database = $$link;
		}
		
		return $$link;
	}
	
	function checkConnection() {
		if( !isset($this->database) ) {
			$this->connect();
		} else {
			
		}
	}
	
	function disconnect($link = '_db_link') {
		//global $$link;
		
		//return mysql_close($$link);
	}
	
	function close($link = '_db_link') {
		global $$link;
		
		return mysql_close($$link);
	}
	
	function error($sql, $errno, $error) {
		die($errno . '<br>' . $error . '<br>' . $sql);
	}
	
	//QUERY
	function query($sql, $link = '_db_link') {
		global $$link;
		
		$this->checkConnection();
		
		//if( $this->enableSQLlog == 'true') ) {
		//	error_log('QUERY ' . $query . "\n", 3, $this->db_logfile);
		//}
		//$result = mysql_query($sql, $$link) or $this->error($sql, mysql_errno(), mysql_error());
		$result = mysql_query($sql, $$link);
		if( !$result ) {
			//return false;
			$this->error($sql, mysql_errno(), mysql_error());
		}
		//if( $this->enableSQLlog == 'true') ) {
		//	error_log('RESULT ' . $result . ' ' . mysql_errno() . ' ' . mysql_error() . "\n", 3, $this->db_logfile);
		//}
		
		//counter
		$this->addQueryCount();
		
		//log
		$this->addQueryLog($sql);
		
		return $result;
	}
	
	function perform($table, $data, $action = 'insert', $parameters = '', $link = '_db_link') {
		reset($data);
		
		if( $action == 'insert' || $action == 'replace' ) {
			//$sql = 'insert into ' . $table . ' (';
			$sql = $action . ' into ' . $table . ' (';
			while( list($columns, ) = each($data) ) {
				$sql .= $columns . ', ';
			}
			$sql = substr($sql, 0, -2) . ') values (';
			reset($data);
			while( list($columns, $value) = each($data) ) {
				switch( (string)$value ) {
					case 'now()':
						$sql .= 'now(), ';
						break;
					case 'add_one()':
						$sql .= $columns . ' + 1, ';
						break;
					case 'null':
						$sql .= 'null, ';
						break;
					default:
						$sql .= '\'' . $this->str_input($value) . '\', ';
						break;
				}
			}
			$sql = substr($sql, 0, -2) . ')';
			
		} elseif( $action == 'update' ) {
			$sql = 'update ' . $table . ' set ';
			while( list($columns, $value) = each($data) ) {
				switch( (string)$value ) {
					case 'now()':
						$sql .= $columns . ' = now(), ';
						break;
					case 'add_one()':
						$sql .= $columns . ' = ' . $columns . ' + 1, ';
						break;
					case 'null':
						$sql .= $columns .= ' = null, ';
						break;
					default:
						$sql .= $columns . ' = \'' . $this->str_input($value) . '\', ';
						break;
				}
			}
			$sql = substr($sql, 0, -2) . ' where ' . $parameters;
		}
		
		return $this->query($sql, $link);
	}
	
	function add($sql) {
		$result = $this->query($sql);
		if( !$result ) {
			return false;
		}
		
		return true;
	}
	
	function insert($sql) {
		$result = $this->query($sql);
		if( !$result ) {
			return false;
		}
		$insert_id = $this->insert_id();
		
		return $insert_id;
	}
	
	function update($sql) {
		$result = $this->query($sql);
		if( !$result ) {
			return false;
		}
		
		return true;
	}
	
	function delete($sql) {
		$result = $this->query($sql);
		if( !$result ) {
			return false;
		}
		
		return true;
	}
	
	function updateField($table, $id, $field, $value, $pk = '') {
		if( $pk == '' ) {
			$pk = $table . '_id';
		}
		$sql = "update " . $table . " set " . $field . " = '" . func_db_input($value) . "' where " . $pk . " = '" . $id . "' limit 1";
		return $this->update($sql);
	}
	
	function updateFieldC($table, $id, $field, $value, $pk = '') {
		if( $pk == '' ) {
			$pk = $table . '_id';
		}
		$count = $this->getCountA($table, " " . $pk . " != '" . $id . "' and " . $field . " = '" . func_db_input($value) . "' ");
		if( $count > 0 ) {
			return -1;
		} else {
			return $this->updateField($table, $id, $field, $value, $pk);
		}
	}
	
	function updateRow($table, $id, $field, $value, $pk = '') {
		if( $pk == '' ) {
			$pk = $table . '_id';
		}
		$sql = "update " . $table . " set " . $field . " = '" . func_db_input($value) . "' where " . $pk . " = '" . $id . "' limit 1";
		return $this->update($sql);
	}
	
	function updateRowC($table, $id, $field, $value, $pk = '') {
		if( $pk == '' ) {
			$pk = $table . '_id';
		}
		$count = $this->getCountA($table, " " . $pk . " != '" . $id . "' and " . $field . " = '" . func_db_input($value) . "' ");
		if( $count > 0 ) {
			return -1;
		} else {
			return $this->updateRow($table, $id, $field, $value, $pk);
		}
	}
	
	function deleteRow($table, $id, $pk = '') {
		if( $pk == '' ) {
			$pk = $table . '_id';
		}
		$sql = "delete from " . $table . " where " . $pk . " = '" . (int)$id . "' limit 1";
		return $this->delete($sql);
	}
	
	function addA($table, $data) {
		$result = $this->perform($table, $data);
		if( !$result ) {
			return false;
		}
		
		return true;
	}
	
	function insertA($table, $data) {
		$result = $this->perform($table, $data);
		if( !$result ) {
			return false;
		}
		$insert_id = $this->insert_id();
		
		return $insert_id;
	}
	
	function updateA($table, $data, $parameters) {
		$result = $this->perform($table, $data, 'update', $parameters);
		if( !$result ) {
			return false;
		}
		
		return true;
	}
	
	function deleteA($table, $parameters) {
		$sql = "delete from " . $table . " where " . $parameters;
		return $this->delete($sql);
	}
	
	//RESULT
	function fetch_array($db_query) {
		return mysql_fetch_array($db_query, MYSQL_ASSOC);
	}
	
	function fetch_array_assoc($db_query) {
		return mysql_fetch_array($db_query, MYSQL_ASSOC);
	}
	
	function fetch_array_num($db_query) {
		return mysql_fetch_array($db_query, MYSQL_NUM);
	}
	
	function fetch_array_both($db_query) {
		return mysql_fetch_array($db_query, MYSQL_BOTH);
	}
	
	function fetch_assoc($db_query) {
		return mysql_fetch_assoc($db_query);
	}
	
	function fetch_object($db_query) {
		return mysql_fetch_object($db_query);
	}
	
	function fetch_objects($db_query) {
		$objects = array();
		
		while($row = mysql_fetch_object($db_query)) {
			$objects[] = $row;
		}
		
		return $objects;
	}
	
	function num_rows($db_query) {
		return mysql_num_rows($db_query);
	}
	
	function affected_rows($link = '_db_link') {
		global $$link;
		return mysql_affected_rows($$link);
	}
	
	function data_seek($db_query, $row_number) {
		return mysql_data_seek($db_query, $row_number);
	}
	
	function getUniqueID($table) {
		$id = $table . "_id";
		$query = "select MAX(" . $id . ") as last_id from " . $table . "";
		//$result = mysql_query($query);
		//$row = mysql_fetch_array($result);
		$result = $this->query($query);
		$row = $this->fetch_array($result);
		
		$next_id = $row['last_id'] + 1;
		return $next_id;
	}
	
	function insert_id() {
		return mysql_insert_id();
	}
	
	function free_result($db_query) {
		return mysql_free_result($db_query);
	}
	
	function fetch_fields($db_query) {
		$fields_array = array();
		$i = 0;
		while($i < mysql_num_fields($db_query)) {
			$meta = mysql_fetch_field($db_query, $i);
			if( !$meta ) {
				$this->error("fetch_fields", "mysql_fetch_field", "");
			}
			
			$fields_array[] = array(
					'blob' => $meta->blob,
					'max_length' => $meta->max_length,
					'multiple_key' => $meta->multiple_key,
					'name' => $meta->name,
					'not_null' => $meta->not_null,
					'numeric' => $meta->numeric,
					'primary_key' => $meta->primary_key,
					'table' => $meta->table,
					'type' => $meta->type,
					'unique_key' => $meta->unique_key,
					'unsigned' => $meta->unsigned,
					'zerofill' => $meta->zerofill,
						);
			$i++;
		}
		
		return $fields_array;
	}
	
	//
	function getOne($sql) {
		$result = $this->query($sql);
		$row = $this->fetch_array($result);
		
		return $row;
	}
	
	function getRow($sql, $field = '') {
		$result = $this->query($sql);
		$row = $this->fetch_array($result);
		
		if( $field == '' ) {
			return $row;
		} elseif( is_array($field) ) {
			$rowb = array();
			foreach($field as $k => $v) {
				if( isset($row[$v]) ) {
					$rowb[] = $row[$v];
				}
			}
			return $rowb;
		} else {
			return $row[$field];
		}
	}
	
	function getRows($sql, $limit = false) {
		$i = 0;
		$result = $this->query($sql);
		while($row = $this->fetch_array($result)) {
			$rows[] = $row;
			if( $limit > 0 && $i > $limit ) {
				break;
			}
			$i++;
		}
		
		return $rows;
	}
	
	function getRowLight($table, $key, $value) {
		$sql = "select * from " . $table . " where " . $key . " = '" . $value . "'";
		return $this->getRow($sql);
	}
	
	function getRowParam($table, $param) {
		$query = "select * from " . $table . " ";
		if( is_array($param) ) {
			foreach($param as $k => $v) {
				if( $i == 0 ) {
					$query .= " where ";
					$query .= " " . $k . " = '" . $v . "' ";
				} else {
					$query .= " and ";
					$query .= " " . $k . " = '" . $v . "' ";
				}
			}
		}
		
		return $this->getRow($sql);
	}
	
	function getCount($sql) {
		$result = $this->query($sql);
		$row = $this->fetch_array($result);
		
		if( is_array($row) ) {
			if( isset($row['count']) ) {
				return $row['count'];
			} elseif( strpos($sql, 'count(') !== false ) {
				return $row['0'];
			} else {
				return $this->num_rows($result);
			}
		}
		
		return 0;
	}
	
	function getField($sql, $field) {
		$row = $this->getRow($sql);
		if( is_array($row) && isset($row[$field]) ) {
			return $row[$field];
		}
		
		return '';
	}
	
	function checkCount($sql, $unique = false) {
		$count = $this->getCount($sql);
		
		if( $unique == true ) {
			return ($count == 1) ? true : false;
		} else {
			return ($count > 0) ? true : false;
		}
	}
	
	//
	function getOneA($table, $parameters = '') {
		if( $parameters == '' ) {
			$parameters = '1';
		}
		$sql = "select * from " . $table . " where " . $parameters;
		
		return $this->getRow($sql);
	}
	
	function getRowA($table, $parameters = '') {
		if( $parameters == '' ) {
			$parameters = '1';
		}
		$sql = "select * from " . $table . " where " . $parameters;
		
		return $this->getRow($sql);
	}
	
	function getRowsA($table, $parameters = '', $limit = false) {
		if( $parameters == '' ) {
			$parameters = '1';
		}
		$sql = "select * from " . $table . " where " . $parameters;
		
		return $this->getRows($sql, $limit);
	}
	
	function getCountA($table, $parameters = '') {
		if( $parameters == '' ) {
			$parameters = '1';
		}
		$sql = "select count(*) as count from " . $table . " where " . $parameters;
		
		return $this->getCount($sql);
	}
	
	function checkCountA($table, $parameters, $unique = false) {
		$count = $this->getCountA($table, $parameters);
		
		if( $unique == true ) {
			return ($count == 1) ? true : false;
		} else {
			return ($count > 0) ? true : false;
		}
	}
	
	//STRING
	function str_output($string) {
		return htmlspecialchars($string);
	}
	
	function str_input($string) {
		return addslashes($string);
	}
	
	function str_prepare_input($string) {
		if( is_string($string)) {
			return trim(util_string_sanitize(stripslashes($string)));
		} elseif( is_array($string)) {
			reset($string);
			while( list($key, $value) = each($string) ) {
				$string[$key] = $this->str_input($value);
			}
			return $string;
		} else {
			return $string;
		}
	}
	
	function escape_string($string) {
		return mysql_escape_string($string);
	}
	
	//META
	function info($link = '_db_link') {
		global $$link;
		
		return mysql_info($$link);
	}
	
	function getDbs($link = '_db_link') {
		global $$link;
		
		$result = mysql_list_dbs($$link);
		if( !$result ) {
			$this->error("mysql_list_dbs", "mysql_list_dbs", "");
		}
		$dbs = array();
		while($row = mysql_fetch_object($result)) {
    		$dbs[] = $row->Database;
		}
		/*
		//
		$i = 0;
		$num_rows = mysql_num_rows($result);
		while($i < $num_rows) {
			$dbs[] = mysql_db_name($result, $i);
			$i++;
		}
		*/
		
		return $dbs;
	}
	
	function getTables($link = '_db_link') {
		global $$link;
		
		$result = mysql_list_tables($this->dbName, $$link);
		if( !$result ) {
			$this->error("mysql_list_tables", "mysql_list_tables", "");
		}
		$tables = array();
		/*
		while($row = mysql_fetch_row($result)) {
			$tables[] = $row['0'];
		}
		*/
		$num_rows = mysql_num_rows($result);
		for($i=0; $i<$num_rows; $i++) {
			$tables[] = mysql_tablename($result, $i);
		}
		mysql_free_result($result);
		
		return $tables;
	}
	
	function getMetadata($table, $link = '_db_link') {
		global $$link;
		
		$count = 0;
		$id = 0;
		$metadata_array = array();
		
		$result = mysql_list_fields($this->dbName, $table, $$link);
		if( $result < 0 ) {
			$this->error("mysql_list_fields", "mysql_list_fields", "");
		}
		$count = mysql_num_fields($result);
		for($i=0; $i<$count; $i++) {
			$field_table = mysql_field_table($result, $i);
			$field_name = mysql_field_name($result, $i);
			$field_type = mysql_field_type($result, $i);
			$field_len = mysql_field_len($result, $i);
			$field_flags = mysql_field_flags($result, $i);
			
			$metadata_array[] = array(
						'table' => $field_table,
						'name' => $field_name,
						'type' => $field_type,
						'len' => $field_len,
						'flags' => $field_flags,
						//'meta' => $i,
								);
		}
		mysql_free_result($result);
		
		return $metadata_array;
	}
	
	function getFields($table, $link = '_db_link') {
		global $$link;
		
		$sql = "select * from " . $table . " where 1 limit 1";
		$result = mysql_query($sql, $$link);
		if( !$result ) {
			$this->error("getFields", "getFields", "");
		}
		
		$fields_array = array();
		$i = 0;
		while($i < mysql_num_fields($result)) {
			$meta = mysql_fetch_field($result, $i);
			if( !$meta ) {
				$this->error("getFields", "mysql_fetch_field", "");
			}
			
			$fields_array[] = array(
					'blob' => $meta->blob,
					'max_length' => $meta->max_length,
					'multiple_key' => $meta->multiple_key,
					'name' => $meta->name,
					'not_null' => $meta->not_null,
					'numeric' => $meta->numeric,
					'primary_key' => $meta->primary_key,
					'table' => $meta->table,
					'type' => $meta->type,
					'unique_key' => $meta->unique_key,
					'unsigned' => $meta->unsigned,
					'zerofill' => $meta->zerofill,
						);
			$i++;
		}
		
		return $fields_array;
	}
	
	function getFieldsA($table) {
		$fields_a = array();
		
		$fields = $this->getFields($table);
		if( is_array($fields) ) {
			for($i=0, $n=sizeof($fields); $i<$n; $i++) {
				$fields_a[] = $fields[$i]['name'];
			}
		}
		
		return $fields_a;
	}
	
	//DB
	function createDatabase($db_name, $link = '_db_link') {
		/*
		//
		global $$link;
		$result = mysql_create_db($db_name, $$link);
		if( $result ) {
			return true;
		}
		*/
		$sql = "CREATE DATABASE '" . $db_name . "'";
		$result = $this->query($sql, $link);
		if( $result ) {
			return true;
		}
		
		return false;
	}
	
	function createTable($table_name, $sql, $link = '_db_link') {
		$tables = $this->getTables($link);
		if( in_array($db_name, $tables) ) {
			return false;
		}
		$charset = '';
		$sql = $this->createTableSQL($sql, $charset);
		
		return $this->query($sql);
	}
	
	function createTableSQL($sql, $charset = '') {
		$type = strtoupper(preg_replace("/^\s*CREATE TABLE\s+.+\s+\(.+?\).*(ENGINE|TYPE)\s*=\s*([a-z]+?).*$/isU", "\\2", $sql));
		$type = in_array($type, array('MYISAM', 'HEAP')) ? $type : 'MYISAM';
		$sql = preg_replace("/^\s*(CREATE TABLE\s+.+\s+\(.+?\)).*$/isU", "\\1", $sql) . (mysql_get_server_info() > '4.1' ? " ENGINE=$type DEFAULT CHARSET=$dbcharset" : " TYPE=$type");
		
		return $sql;
	}
	
	function getIncrementID($table) {
		$row = $this->getRow("SHOW TABLE STATUS LIKE '" . $table . "'");
		if( $row['Auto_increment'] ) {
			return $row['Auto_increment'] - 1;
		}
		
		return 0;
	}
	
	function getMaxID() {
		$row = $this->getRow("SHOW TABLE STATUS LIKE '" . $table . "'");
		if( $row['Auto_increment'] ) {
			return $row['Auto_increment'] - 1;
		}
		
		return 0;
	}
	
	//query Count Operation
	function addQueryCount() {
		global $system_db_query_count;
		if( !isset($system_db_query_count) ) {
			$system_db_query_count = 0;
		}
		
		$system_db_query_count++;
		$this->queryCount++;
	}
	
	function getQueryCount() {
		global $system_db_query_count;
		
		if( isset($system_db_query_count) ) {
			return $system_db_query_count;
		}
		return $this->queryCount;
	}
	
	//
	function addQueryLog($sql) {
		global $system_db_query_logs;
		if( isset($system_db_query_logs) && $system_db_query_logs == -1 ) {
			return false;
		}
		global $system_db_query_log;
		if( !isset($system_db_query_log) ) {
			$system_db_query_log = array();
		}
		
		$system_db_query_log[] = $sql;
	}
	
	function getQueryLog() {
		global $system_db_query_log;
		
		if( isset($system_db_query_log) ) {
			return $system_db_query_log;
		}
		return $this->queryLog;
	}
	
	//
	function getCachedRow($sql) {
		
	}
	
	//
	function _get_hash_key($key) {
		return abs(crc32($key)) . '_' . md5($key);
	}
}
//
?>